package academico.persistence;

import academico.model.Aluno;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AlunoDAOPsql implements AlunoDAO{
    private static Connection connection;

    public static String DRIVER = "org.postgresql.Driver";
    public static String URL = "jdbc:postgresql://localhost:5432/BDLP";
    public static  String USER="dai3s2010";
    public static String PASSWD = "lp2";

    public AlunoDAOPsql() {
        super();
    }

    public static Connection getConnection(){
        if (connection !=null){
                return connection;
        } else {
            try {
                Class.forName(DRIVER).newInstance();
                connection = DriverManager.getConnection(AlunoDAOPsql.URL, AlunoDAOPsql.USER, AlunoDAOPsql.PASSWD);
            } catch (InstantiationException e) {
                e.printStackTrace();
                return null;
            } catch (IllegalAccessException e) {
                e.printStackTrace();
                return null;
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                return null;
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        }
        return connection;
    }

    public static int getNextId(){
        Connection conn = AlunoDAOPsql.getConnection();
        Statement stmt;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
        String sql = "select max(id)from tb_aluno";

        try {
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()){
                int id = rs.getInt(1);
                return id;
            }

        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }

        return 0;
    }

    public boolean armazenar(Aluno aluno) {
        aluno.setId(AlunoDAOPsql.getNextId() + 1);
        Connection conn = AlunoDAOPsql.getConnection();
        Statement stmt;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

        String sql = "insert into tb_aluno (id,nome,sobrenome)values(" + aluno.getId() + ",'" + aluno.getNome() + "','" + aluno.getSobrenome()+ "')";

        try {
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public Aluno recuperar(int idref) {
        Aluno alunoaux = null;
        Connection conn = AlunoDAOPsql.getConnection();
        Statement stmt;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        String sql = "select id, nome, sobrenome from tb_aluno where id = " + idref;

        try {
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()){
                int id = rs.getInt(1);
                String nome = rs.getString(2);
                String sobrenome = rs.getString(3);
                alunoaux = new Aluno();
                alunoaux.setId(id);
                alunoaux.setNome(nome);
                alunoaux.setSobrenome(sobrenome);
            }

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

        return alunoaux;
    }

    public boolean atualizar(Aluno aluno) {
        Connection conn = AlunoDAOPsql.getConnection();
        Statement stmt;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        String sql = "update tb_aluno set nome= '" + aluno.getNome() + "', sobrenome = '" + aluno.getSobrenome() + "' where id = " + aluno.getId();
        try {
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean remover(int id) {
        Connection conn = AlunoDAOPsql.getConnection();
        Statement stmt;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        String sql = "delete from tb_aluno where id =" + id ;

        try {
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public List<Aluno> listar() {
        List<Aluno> lista = new ArrayList<Aluno>();
        Connection conn = AlunoDAOPsql.getConnection();
        Statement stmt;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        String sql = "select id,nome,sobrenome from tb_aluno";

        try {
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                int id = rs.getInt(1);
                String nome = rs.getString(2);
                String sobrenome = rs.getString(3);
                Aluno alunoaux = new Aluno();
                alunoaux.setId(id);
                alunoaux.setNome(nome);
                alunoaux.setSobrenome(sobrenome);
                lista.add(alunoaux);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

        return lista;
    }

}
